package dao;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;

import conector.mysql;

public class clubsDao {
	
	static String CAMPOS_TABLA_CLUBS = "id,codigo_club,nombre,poblacion,estado";

	public ResultSet getAllPoblaciones(){
		String queryString ="SELECT "+CAMPOS_TABLA_CLUBS+" FROM clubs";
		ResultSet resultSet = mysql.queryObj(queryString);
		return resultSet;
	}
	
	public int countAllClubs() throws SQLException{
		String queryString="SELECT COUNT(id) AS resultado FROM clubs";
		ResultSet resultSet = mysql.queryObj(queryString);
		while (resultSet.next()){
			int valor =  resultSet.getInt("resultado");
			return valor;
		}
		return 0;
	}
	
	public ResultSet getRandomClub(int id_competicion,int temporada) throws SQLException{
		String queryString="SELECT * FROM clubs t1 WHERE NOT EXISTS (SELECT * FROM equipos t2 WHERE t1.codigo_club = t2.id_club AND t2.id_competicion="+id_competicion+" AND t2.temporada="+temporada+") ORDER BY RAND() LIMIT 0,1";
		System.out.println(queryString);
		ResultSet resultSet = mysql.queryObj(queryString);
		return resultSet;
	}
	
		
	public void crearTablaClubs() throws IOException, SQLException{
		String queryString ="CREATE TABLE clubs (id int(11) NOT NULL AUTO_INCREMENT,codigo_club int(11) NOT NULL,nombre varchar(100) DEFAULT NULL,poblacion varchar(75) DEFAULT NULL,estado tinyint(1) DEFAULT NULL, PRIMARY KEY (id,codigo_club), KEY id (id), KEY `CODIGO_CLUB` (`codigo_club`)) ENGINE=InnoDB AUTO_INCREMENT=1749 DEFAULT CHARSET=utf8";
		mysql.executeUpdate(queryString);
	}
	
	public int getMaxCodigoClub() throws SQLException{
		String queryString="SELECT MAX(codigo_club) as max FROM clubs";
		ResultSet resultSet = mysql.queryObj(queryString);
		while (resultSet.next()){
			int valor =  resultSet.getInt("max");
			return valor;
		}
		return 0;
	}
	
	
	public int getMinCodigoClub() throws SQLException{
		String queryString="SELECT MIN(codigo_club) as min FROM clubs";
		ResultSet resultSet = mysql.queryObj(queryString);
		while (resultSet.next()){
			int valor =  resultSet.getInt("min");
			return valor;
		}
		return 0;
	}
	
	public boolean comprobarExistenciaClub(int codigoClub,int id_competicion,int temporada) throws SQLException{
		String queryString ="SELECT COUNT(id_club) AS resultado FROM equipos WHERE id_club="+codigoClub+" AND id_competicion="+id_competicion+" AND temporada="+temporada;
		ResultSet resultSet = mysql.queryObj(queryString);
		while (resultSet.next()){
			boolean valor =  resultSet.getBoolean("resultado");
			return valor;
		}
		return false;
	}

	public int getRandomClub() throws SQLException{
		String queryString="SELECT codigo_club FROM clubs ORDER BY RAND()LIMIT 1";
		ResultSet resultSet = mysql.queryObj(queryString);
		while (resultSet.next()){
			int valor =  resultSet.getInt("codigo_club");
			return valor;
		}
		return 0;
	}
}
